Boolean logic instead of CASE WHEN clause
Overview
Here, here and here I have written about the CASE WHEN clause and its application in real life. We could use other functions with the same task or we could use boolean operators.
Programmers use this approach for decades. It's simple to use and often outperforms ternary operators and control flow operators (recently this tendency come to naught).
Let's look at an example. We want to select order's positions. If the position's status equals to 0 we want the price lower than $300. If position's stats is equals to 1 take positions with price lower than $400. With CASE WHEN clause, we will use this query:
SELECT * FROM positions WHERE price < CASE status_id WHEN 0 THEN 300 WHEN 1 THEN 400 END
If we want to use boolean logic instead we should follow next rules:
1) conditions for price and status_id combined by AND operator;
2) major condition blocks combined by OR operator.
In this way query will be look like this:
SELECT * FROM positions WHERE status_id = 0 AND price < 300 OR status_id = 1 AND price < 400
The advantage of boolean logic to a CASE WHEN clause is query execution speed. In case of boolean logic RDBMS could easily manage the query execution plan and indexes.
Of course, this trick work in both directions. If you have a lot of different conditions for price and haven't any time limits. Than for the sake of readability you can convert complex condition with OR and AND to the clear CASE version :)